USE SAS
GO

 IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'GetProductDetails')
	BEGIN
		DROP  Procedure  dbo.GetProductDetails
	END
GO

CREATE Procedure [dbo].[GetProductDetails]
	@PKID		varchar(20) = ''
AS
BEGIN

	declare 
		@sql			varchar(max)


	SELECT	@sql = ''

	IF NOT EXISTS (SELECT 1 FROM TR_PRODUCT WHERE ProductID  = @PKID)
		SET @PKID = ''


	;WITH Prod AS (
		SELECT 
			p.ProductID			AS ProductID
		,	p.ProductNo			AS ProductNo
		,	p.ProductDesc		AS ProductDesc
		,	p.UnitPrice			AS UnitPrice
		,	p.IsActive			AS IsActive
		FROM TR_PRODUCT as p WITH (NOLOCK)
		WHERE
			p.ProductID = ISNULL(@PKID, 0)
		UNION
		SELECT
			0					AS ProductID
		,	''					AS ProductNo
		,	''					AS ProductDesc
		,	0.00				AS UnitPrice
		,	1					AS IsActive
		FROM TR_PARTNER as p WITH (NOLOCK)
		WHERE
			ISNULL(@PKID, '') = ''
	), FinalXML AS (
		SELECT 1 AS ID, N'<DATA>' AS Details 
		UNION
		SELECT 2 AS ID, N'<RESPONSE><RESULT>S</RESULT><MESSAGE></MESSAGE></RESPONSE>'
		UNION
		SELECT
			3 AS ID, 
		+	'<PKID><![CDATA[' + CAST(p.ProductID AS VARCHAR) + ']]></PKID>'
		+	'<PRODUCTID><![CDATA[' + CAST(p.ProductID AS VARCHAR) + ']]></PRODUCTID>'
		+	'<PRODUCTNO><![CDATA[' + ISNULL(p.PRODUCTNO,'') + ']]></PRODUCTNO>'
		+	'<PRODUCTDESC><![CDATA[' + ISNULL(p.ProductDesc,'') + ']]></PRODUCTDESC>'
		+	'<UNITPRICE><![CDATA[' + ISNULL(CAST(p.UnitPrice AS VARCHAR),'0')+ ']]></UNITPRICE>' 
		+	'<ISACTIVE><![CDATA[' + (CASE WHEN ISNULL(IsActive,1) = 1 THEN 'X' ELSE '' END) + ']]></ISACTIVE>' 
					AS Details
		FROM Prod as p
		UNION
		SELECT 4 AS ID, '</DATA>' AS Details 
	)

	SELECT @sql = @sql + Details
	FROM FinalXML
	Order by ID

	select @sql	AS Detail
END



